| |
168 Search Results Found |
| 1 |
Forum: Data Guard «»
Posted on: Sat, 11 February 2023 11:31 «»
By: Andrey_R
Re: RMAN-06820 on rman backup from standby with OS authentication
…you actually do the test, rather than merely speculating, it may become clear that the datafile backup is 100% useless without the redo required to make it consistent.
That's a fair point. So I did:
- Install Oracle 19c EE, no PDB, on Windows Server…
|
| 2 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 13:32 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…is another possible version with tests of various number values for n.
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID…
|
| 3 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 13:30 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…see some problems with that. Please see my tests below using various number values for n, including 1, which returns no rows.
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, …
|
| 4 |
Forum: Text & interMedia «»
Posted on: Thu, 10 August 2023 19:05 «»
By: Barbara Boehmer
Re: How to determine last tiime Content was indexed
…see the following demonstration and answers below.
-- table, initial data, index, additional data for testing:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_tab
2 (id NUMBER,
3 document VARCHAR2(…
|
| 5 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 10 March 2023 13:25 «»
By: Barbara Boehmer
Re: Suggestion on count via sql statement
…method:
SCOTT@orcl_12.1.0.2.0> SELECT Name, TRUNC(TEST_Date) AS Test_date, Instrument, COUNT(*) AS count
2 FROM test
3 WHERE Instrument IN ('Down', 'Not available')
4 GROUP BY Name, TRUNC(TEST_Date), Instrument
5 UNION
6 …
|
| 6 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 05 April 2023 12:25 «»
By: Barbara Boehmer
Re: difficult task
…tables and such look good, although you may want to make changes later.
For your procedure, this is what you have so far.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE calculate_positions(
2 vInDateTime IN DATE;
3 )
4 AS
5 …
|
| 7 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 09 February 2024 13:17 «»
By: Barbara Boehmer
Re: Xmlattribute not returning tag when the value is null
…am assuming that this is a simplification, and that your data comes from a table with multiple rows and that any value in any column in any row may be null. If this is the case, then after reviewing some of the links from the search link that Michel…
|
| 8 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 08 February 2023 20:27 «»
By: Barbara Boehmer
Re: Convert Date formats
…should be comparing dates as dates, not converting them to character strings and comparing those.
Assuming that you have the following table and data
SCOTT@orcl_12.1.0.2.0> create table mytable (date2 date)
2 /
Table created.
SCOTT@…
|
| 9 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 10:35 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID
--------------- ----------
1 165949
1 165950…
|
| 10 |
Forum: Server Administration «»
Posted on: Tue, 24 May 2022 04:08 «»
By: shamsad.khan@gmail.com
Unable to connect to pluggable database - Oracle Datase 12.2.0.1.0
…
I have installed oracle database 12.2.0.1.0. as container database with orclpdb as my pluggabe database
Logged in sqlplus as connect sys as sysdba
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE SCOPE=BOTH;
create user c##test identified by …
|
| 11 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 17 April 2024 16:46 «»
By: Barbara Boehmer
Re: Oracle ORA-00918: column ambiguously defined
…I am trying to understand what causes an error, I try to narrow it down to the simplest example that I can that reproduces the error and the simplest thing that resolves it. Please see the simplified demonstration below in which the problem is …
|
| 12 |
Forum: SQL & PL/SQL «»
Posted on: Sun, 17 December 2023 20:26 «»
By: Barbara Boehmer
Re: Problem in pivot query
…@OraFerro,
I strongly suspect that you have a minor typing error in your query that is not being posted here. You need to post a copy and paste of an actual complete run as I have done below, including the view creation, including line numbers, …
|
| 13 |
Forum: Server Utilities «»
Posted on: Fri, 28 July 2023 12:28 «»
By: Barbara Boehmer
Re: how to parametrize values in pl/sql block
…see the changes to line 34 below. It should work with multiple tables or just one. It will just use an IN clause instead of =.
SCOTT@orcl_12.1.0.2.0> create or replace procedure test_proc
2 (p_schema in …
|
| 14 |
Forum: Server Utilities «»
Posted on: Fri, 28 July 2023 04:49 «»
By: Barbara Boehmer
Re: how to parametrize values in pl/sql block
…is an example for you.
SCOTT@orcl_12.1.0.2.0> create or replace procedure test_proc
2 (p_schema in varchar2,
3 p_table in varchar2)
4 as
5 l_dp_handle …
|
| 15 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 03 February 2023 15:28 «»
By: Barbara Boehmer
Re: XML parse
…would help to know where your data comes from, how you retrieve it, how it is stored, and what sort of table you want to load it into. In the following I have assumed that it is in a text file on your server. I suggest that you start with the basics and…
|
| 16 |
Forum: Text & interMedia «»
Posted on: Mon, 16 January 2023 11:28 «»
By: Barbara Boehmer
Re: Oracle Text Search - Handling special characters and blank search term
…the translate function, it translates each occurrence of the character in the second parameter to the corresponding character in the third parameter, but just removes others. In the original example, there were only three special characters, so I used …
|
| 17 |
Forum: Text & interMedia «»
Posted on: Tue, 10 January 2023 12:15 «»
By: Barbara Boehmer
Re: Oracle Text Search - Handling special characters and blank search term
…are a number of issues here. One issue is that Oracle Text does not know what to do with the special characters. Another is that if the text query is not executed and there is no score then it does not know what to do when you have selected that …
|
| 18 |
Forum: SQL & PL/SQL «»
Posted on: Sat, 16 December 2023 17:29 «»
By: Barbara Boehmer
Re: Problem in pivot query
…sum(wtd_year) to sum(wtd_amount)
SCOTT@orcl_12.1.0.2.0> select banner from v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12…
|
| 19 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 18 September 2023 11:41 «»
By: Barbara Boehmer
Re: how to get employee in and out time for generated query based on first in and last out
…the following minimal example, dates with only one punch time will have the same date for in and out times. Also, whatever your first column is "M." (machine?) is ignored. It is ordered by the punch_date, which is really just a …
|
| 20 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 07 February 2023 13:48 «»
By: Barbara Boehmer
Re: XML parse
…are two issues here.
You have multiple LEI under different categories, so those can be given different column names, just as the different Id were named FrId and ToId.
You have multiple Rpt under TradData, so you need an additional XMLTABLE. You …
|
| 21 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 06 November 2023 18:02 «»
By: Barbara Boehmer
Re: Recursive subquery factoring
…following applies a function to one column of your existing view.
-- function and query:
create or replace function get_path_id_post_d
(p_path_id_post in varchar2)
return varchar2
as
v_path_id_post_d &…
|
| 22 |
Forum: SQL & PL/SQL «»
Posted on: Sun, 01 October 2023 03:57 «»
By: Barbara Boehmer
Re: Grouping similar strings together using UTL_MATCH Jaro-Winkler
…following produces the same results as your query, but in a slightly different format. It eliminates the possibility of having things that are not closely related in the same group, due to a chain of relations, by comparing collections and only …
|
| 23 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 29 March 2023 19:55 «»
By: Barbara Boehmer
Re: sql query
…
SCOTT@orcl_12.1.0.2.0> COLUMN mod_flag FORMAT A8
SCOTT@orcl_12.1.0.2.0> -- test data you provided:
SCOTT@orcl_12.1.0.2.0> WITH
2 students (id, term, module) AS
3 (SELECT 123, 2023, 2100 FROM DUAL UNION ALL
4 SELECT 123, …
|
| 24 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 03 March 2023 11:24 «»
By: Barbara Boehmer
Re: Identify Groups where 2 or more Clients have bought the same Products - SQL Oracle
…for creating table and data that you should have provided; please do so yourself next time:
CREATE TABLE table1
(group_id VARCHAR2(8),
client_id VARCHAR2(9),
product_id VARCHAR2(10),
prod_qty NUMBER)
/
INSERT ALL
INTO table1 …
|
| 25 |
Forum: Forms «»
Posted on: Sat, 13 August 2022 03:29 «»
By: sasipalarivattom
Re: Files generated on server with sufix
…Hi,
How are you generating the report?
Have you tried generating report in URL
like http://WIN-SERV:9002/reports/rwservlet?server=rep_server1&report=test.rdf&destype=file&desformat=html&userid=scott/ti ger@orcl&desname=c:\rep1.…
|
| 26 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 23 October 2018 22:23 «»
By: Barbara Boehmer
Re: PLS-00201: identifier 'CTX_DDL' must be declared
…following is just a demonstration of what has already been said, using user test. You will need to substitute your actual user. You can also grant privileges from CTXSYS or from a user with DBA privileges.
-- reproduction of problem:
SCOTT@orcl_12…
|
| 27 |
Forum: Server Utilities «»
Posted on: Mon, 30 April 2018 18:26 «»
By: Barbara Boehmer
Re: Bulk insert with SQL*Loader fails to abort inserts on index errors
… source
Am I to understand that by using Direct Loads we cannot prevent data from being inserted when there is an constraint error?
Direct path loading handles different types of constraints in different ways. You cannot prevent duplicates from …
|
| 28 |
Forum: Server Utilities «»
Posted on: Fri, 23 March 2018 13:59 «»
By: Barbara Boehmer
Re: Issue with SQL LOADER Control file
…joy division suggested does work, as demonstrated below.
SCOTT@orcl_12.1.0.2.0> host type feed_file.dat
No|Name|ID|Location|value
12|simple||Singapore|gold
34|barate|TEST|London|siler
45|sdfsdfs||America|diamond
SCOTT@orcl_12.1.0.2.0> …
|
| 29 |
Forum: Performance Tuning «»
Posted on: Thu, 08 February 2018 06:20 «»
By: juniordbanewbie
Re: Is there a way to find out activities due to redo logs
…Michel,
below supplemental logging not enable
SYS@ol73-12102-se-si orcl>select table_space, username, seg_name,count(0) archivelog_count from v$logmnr_contents where seg_name is not null
2 AND SEG_NAME NOT LIKE '%$'
3 AND SEG_OWNER NOT IN…
|
| 30 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 29 January 2018 17:04 «»
By: Barbara Boehmer
Re: need two token matching query
…of the purpose of asking you to post a copy and paste of a run of the code from SQL*Plus on your system is to be certain of what you have actually run. In your previous post, what you posted did not produce the results you claim to have gotten, so it was…
|
| 31 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 22 January 2018 05:21 «»
By: mvrkr44
Re: need two token matching query
…Barbara Boehmer ,
I have used the punctuations with the given solution and i inserted the below records,but i am not getting the expected result
insert all
into test_names values (7,'rajesh.','kumar',null,null,null)
into test_names values (…
|
| 32 |
Forum: General «»
Posted on: Fri, 05 January 2018 07:34 «»
By: dba4oracle
Re: SQL Report running manually not through cron
…pasting 3 files used as below owned by oracle
3 files as below
1)
connection.ctl
connection.ctl
username = scott
password = tiger
sid = orcl
ORACLE_HOME = /u01/app/oracle
export username
export password
export sid
export ORACLE_HOME
…
|
| 33 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 20 December 2017 00:27 «»
By: mvrkr44
Re: need two token matching query
…Barbara Boehmer .
Now little bit requirement changed.
SCOTT@orcl_12.1.0.2.0> -- test table and data:
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 firstname …
|
| 34 |
Forum: SQL & PL/SQL «»
Posted on: Thu, 07 December 2017 15:33 «»
By: Barbara Boehmer
Re: need two token matching query
…-- test table and data:
SCOTT@orcl_12.1.0.2.0> create table test_names(id number,firstname varchar2(100),secondname varchar2(100),thirdname varchar2(100),fourthname varchar2(100),fifthname varchar2(100));
Table created.
SCOTT@orcl_12.1.0.2.0&…
|
| 35 |
Forum: Server Utilities «»
Posted on: Thu, 30 November 2017 14:56 «»
By: Barbara Boehmer
Re: Load WKT using sql*loader
…your nls_numeric_characters in your data and your database are different and you found a workaround using to_number and replace.
If you want to use one control file to load points and polygons, you can use two separate INTO and WHEN clauses, as shown …
|
| 36 |
Forum: Text & interMedia «»
Posted on: Mon, 10 July 2017 06:01 «»
By: Barbara Boehmer
Re: Matching query
…
SCOTT@orcl_12.1.0.2.0> Create table test(id number,name varchar2(250))
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert all
2 into test values(1,'rajesh kumar reddy')
3 into test values(2,'rajesh kumar mudimela')
4 into test …
|
| 37 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 28 April 2017 17:31 «»
By: Barbara Boehmer
Re: Multiple rows based on column
…-- test data that you provided:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM dept1
2 /
DEPTNO DNAME LOC NOAUTH
---------- -------------- ------------- ----------
10 ACCOUNTING NEW YORK 1…
|
| 38 |
Forum: Data Guard «»
Posted on: Tue, 21 March 2017 23:29 «»
By: prashanthk.dbafreelanzee
Switch over Primary Database TO Standby Database via SQL COMMAND
…Hi, I have recently configured ---- Primary Database and Physical Standby Database on same Machine (Test case).
Primary Database : orcl
Standby Database : orcl_stby
I have too configured - DGMRL , Successfully.
I just want to clear concept of &…
|
| 39 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 24 February 2017 15:26 «»
By: Barbara Boehmer
Re: How to find No of Entries per week For consecutive week
…previously wrote,
I interpreted that to mean the greatest date in the most recent set of at least 1 accessdate in four consecutive weeks for each member.
It now looks like, based on the results that you want, that you mean the first date at which …
|
| 40 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 21 February 2017 06:35 «»
By: Barbara Boehmer
Re: Update based on grouping
…the numbers are used only to eliminate duplication and it does not matter which id ends up with v2 value of o2 or o3, then you could do it all in one update, by trimming the trailing numbers during comparison and update, as demonstrated below.
SCOTT@…
|
Blog
Search
Help
Members
Register
Login
Home